package com.l2j.test.web.admin.controller.execl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.l2j.test.model.entity.LinkContrSignSerialNum;
import com.l2j.test.web.admin.mapper.LinkContrSignSerialNumMapper;
import com.l2j.test.web.admin.service.LinkContrSignSerialNumService;
import com.l2j.test.web.admin.service.impl.LinkContrSignSerialNumServiceImpl;
import com.l2j.test.web.admin.vo.excel.SerialNumVo;
import jakarta.servlet.ServletOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.beans.BeanUtils;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@RestController
public class ExcelExportController {

    @Autowired
    private LinkContrSignSerialNumService linkContrSignSerialNumService;

    @GetMapping("/export/excel")
    public ResponseEntity<byte[]> exportToExcel() throws IOException {

        //获取全部产品分类、产品渠道
//        List<LinkContrSignSerialNum> list = linkContrSignSerialNumService.lambdaQuery()
//                .select(LinkContrSignSerialNum::getClassCode, LinkContrSignSerialNum::getProductAttribute)
//                .groupBy(LinkContrSignSerialNum::getClassCode, LinkContrSignSerialNum::getProductAttribute)
//                .list();
//        System.out.print(list);
        //获取签约序号信息
        //模拟输入参数
        String parameter = "ECAF01,EPAAA02-04,EPAAA93,EPAAB01-99,EPAAD20-21,EPAAD30,EPAAD92-93,EPAAE01-99,EPAAH01-99,EPAAI11-14,EPAAI92,EPAAI94,EPAAJ01-99,EZAAA01-99,EZAAB01-99,EZAAD01-99,EZAAE01-99,EZAAI01-99,EZAAJ01-99,EZABA01-99,EZABD01-99,EZABE01-99,EZADF01-99";
        // 正则表达式
        String regex = "([A-Za-z]+)(\\d+)-(\\d+)|([A-Za-z]+)(\\d+)";
        LambdaQueryWrapper<LinkContrSignSerialNum> wrapper = new LambdaQueryWrapper<>();
        wrapper.select(LinkContrSignSerialNum::getSerialNumber, LinkContrSignSerialNum::getClassCode, LinkContrSignSerialNum::getProductAttribute);
        // 创建 Pattern 对象
        Pattern pattern = Pattern.compile(regex);
        // 创建 Matcher 对象
        Matcher matcher = pattern.matcher(parameter);
        // 查找所有匹配项
        while (matcher.find()) {
            // 如果是带范围的条件 (如 A1-3)
            if (matcher.group(1) != null && matcher.group(2) != null && matcher.group(3) != null) {
                String column = matcher.group(1);
                String start = matcher.group(2);
                String end = matcher.group(3);
                wrapper.or().eq(LinkContrSignSerialNum::getSerialNumberPrefix, column).between(LinkContrSignSerialNum::getSerialNumberValue, start, end);
            }
            // 如果是单个值的条件 (如 D10)
            else if (matcher.group(4) != null && matcher.group(5) != null) {
                String value = matcher.group(4) + matcher.group(5);
                wrapper.or().eq(LinkContrSignSerialNum::getSerialNumber, value);
            }
        }
        List<LinkContrSignSerialNum> list1 = linkContrSignSerialNumService.list(wrapper);
        Set<String> classCode = new LinkedHashSet<>();
        Set<String> productAttribute = new LinkedHashSet<>();
        //用Set提取品类、产品属性的唯一值
        for (int i = 1 ; i < list1.size() ; i++) {
            classCode.add(list1.get(i).getClassCode());
            productAttribute.add(list1.get(i).getProductAttribute());
        }
        String[] classCodeArray = classCode.toArray(new String[0]);
        String[] productAttributeArray = productAttribute.toArray(new String[0]);
        for(String p :productAttributeArray) {
            System.out.println(p);
        }
        List<SerialNumVo> serialNumVoList = list1.stream()
                .map(entity -> {
                    SerialNumVo vo = new SerialNumVo();
                    BeanUtils.copyProperties(entity, vo);
                    return vo;
                })
                .toList();
        for (SerialNumVo serialNumVo : serialNumVoList) {
            serialNumVo.setX(Arrays.asList(productAttributeArray).indexOf(serialNumVo.getProductAttribute()) + 1);
            serialNumVo.setY(Arrays.asList(classCodeArray).indexOf(serialNumVo.getClassCode()) + 1);
        }
        for (SerialNumVo serialNumVo : serialNumVoList) {
            System.out.println(serialNumVo);
        }
        //将同一单元格的签约号累加
        int xMax = serialNumVoList.stream().mapToInt(SerialNumVo::getX).max().orElse(0);
        int yMax = serialNumVoList.stream().mapToInt(SerialNumVo::getY).max().orElse(0);
        String[][] matrix = new String[xMax + 1][yMax + 1];
        for(SerialNumVo serialNumVo : serialNumVoList) {
            int x = serialNumVo.getX();
            int y = serialNumVo.getY();
            if(matrix[x][y] == null) {
                matrix[x][y] = serialNumVo.getSerialNumber();
            }else {
                matrix[x][y] = matrix[x][y] + '\n'+serialNumVo.getSerialNumber();
            }
        }
        //填充数据，Excel是Workbook,返回的是通过Workbook创建的Sheet,填充的对象是通过sheet创建的Row、Cell
        ByteArrayOutputStream wbStream = new ByteArrayOutputStream();
        try(Workbook wb = new XSSFWorkbook()) {
            Sheet sheet1 = wb.createSheet("签约号清单");
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            for(int i = 1 ;i<=yMax;i++) {
                Row row = sheet1.createRow(i +1);
                for(int j = 1 ;j<=xMax;j++) {
                    Cell cell = row.createCell(j);
                    if(matrix[j][i] != null){
                        cell.setCellValue(matrix[j][i]);
                        cell.setCellStyle(cellStyle);
                    }
                }
            }
            //补充具体品类、产品属性
            int startRow = 2;//起始行
            for(String item : classCodeArray){
                Row row = sheet1.getRow(startRow);
                if(row != null) {
                    Cell cell = row.getCell(0);
                    if(cell != null) {
                        cell.setCellValue(item);
                    }else {
                        cell = row.createCell(0);
                        cell.setCellValue(item);
                    }

                }
                startRow = startRow +1;
            }
            int startCol = 1;
            Row row = sheet1.createRow(1);
            for(String item : productAttributeArray){
                if(row != null) {
                    Cell cell = row.getCell(startCol);
                    if(cell != null) {
                        cell.setCellValue(item);
                    }else {
                        cell = row.createCell(startCol);
                        cell.setCellValue(item);
                    }
                }
                startCol = startCol +1;
            }
            wb.write(wbStream);
        }


        //test创建Excel
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("数据表");

        //创建样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 模拟数据
        List<List<Object>> data = Arrays.asList(
                Arrays.asList(1, "张三", 25, "男", "深圳", "打野"),
                Arrays.asList(2, "李四1213800", 30, "男", "杭州", "打野"),
                Arrays.asList(2, "李四1213800", 30, "男", "北京", "打野"),
                Arrays.asList(2, "李四1213800", 30, "男", "上海", "打野"),
                Arrays.asList(2, "李四1213800", 30, "男", "北京", "打野"),
                Arrays.asList(3, "王八", 28, "男", "广州", "打野"),
                Arrays.asList(2, "李四1213800", 30, "男", "北京", "打野")
        );
        //获取全部分类
        // 自定义 Comparator
        List<String> cities = new ArrayList<>();
        cities.add("广州");
        cities.add("北京");
        cities.add("上海");
        Comparator<String> customComparator = (city1, city2) -> {
            int c1 = cities.indexOf(city1);
            int c2 = cities.indexOf(city2);
            if (c1 != -1 && c2 != -1) {
                //return 0; // -1： city1 、 city2 ； 0：舍弃 ；1： city2 、 city1
                return Integer.compare(c1, c2);
            } else if (c1 != -1) {
                return -1; // city1 是优先城市，排前面
            } else if (c2 != -1) {
                return 1;  // city2 是优先城市，排前面
            } else {
                return city1.compareTo(city2); // 都不是优先城市，按自然顺序
            }
        };
        //使用TreeMap实现
        TreeMap<String, Integer> map = new TreeMap<>(customComparator);
        for (List<Object> row : data) {
            if (row.size() > 5) {
                if (map.get(row.get(4).toString()) != null) {
                    map.put(row.get(4).toString(), map.get(row.get(4).toString()) + 1);
                } else {
                    map.put(row.get(4).toString(), 1);
                }
            }
        }
        for (String key : map.keySet()) {
            //System.out.println(key + ":" + map.get(key));
        }
        //创建一级表头
        Row firstHeaderRow = sheet.createRow(0);

        // 创建二级表头
        String[] headers = {"ID", "名称", "年龄"};
        Row headerRow = sheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(headers[i]);
        }
        // 填充数据
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 2);
            List<Object> rowData = data.get(i);
            for (int j = 0; j < rowData.size(); j++) {
                Cell cell = row.createCell(j);
                Object value = rowData.get(j);
                if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof String) {
                    cell.setCellValue((String) value);
                }
            }
        }
        CellRangeAddress cellAddresses = new CellRangeAddress(5, 5, 0, 5);
        sheet.addMergedRegion(cellAddresses);


        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        //自动调整列宽后，宽少于1200的，设置为1200
        for (int i = 0; i < headers.length; i++) {
            if (sheet.getColumnWidth(i) < 1200) {
                sheet.autoSizeColumn(i, false);
                sheet.setColumnWidth(i, 1200);
            }
        }
        // 将 Excel 写入字节数组输出流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        // 设置响应头
        HttpHeaders headersResponse = new HttpHeaders();
        //headersResponse.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        //headersResponse.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
        //headersResponse.setContentDispositionFormData("attachment", "00.xlsx");
        String fileName = URLEncoder.encode("测试文件.xlsx", "UTF-8").replaceAll("\\+", "%20");
        headersResponse.setContentDispositionFormData("attachment", fileName);

        return ResponseEntity.ok()
                .headers(headersResponse)
                //.body(outputStream.toByteArray());
                .body(wbStream.toByteArray());
    }
}

